package com.minisoft.ydac;

import cn.hutool.core.lang.Console;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import io.swagger.models.auth.In;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author JueSu
 * @description: TODO
 * @date 2023/9/7 16:34
 */
public class FlushBuildingInfo {
    public static String DB_YDAC = "ydac-dev";
    public static void main(String[] args) {
        ExcelReader reader = ExcelUtil.getReader("d:\\temp\\buildingInfo.xlsx");
        List<Map<String, Object>> rows = reader.readAll();
        int i = 1;
        //根据id的值更新其它字段信息unitPrice,unitPriceTail,parkingPrice,parkingPriceTail,houseStatus,parkingStatus,propertyYears,buildingType，表名为building_info
        for (Map<String, Object> row : rows) {
            String saleOrgId = row.get("saleOrgId").toString();
            Object groupId = row.get("groupId");
            Object id = row.get("id");
            Map<String, Object> updateFields = new HashMap<>();
            String unitPrice = row.get("unitPrice").toString();
            //对车位进行处理
            if (unitPrice.contains("万元/个")) {
                String parkingPrice = unitPrice.replace("万元/个", "");
                updateFields.put("parkingPrice", parkingPrice);
                updateFields.put("parkingPriceTail", "万元/个");
                updateFields.put("parkingStatus", "车位在售");
                unitPrice = "";
            }
            //如果价格是数值+单位，则把状态设置为在售
            if (unitPrice.matches("^[\\d.]+\\D+")) {
                updateFields.put("houseStatus", "房源在售");
            }
            updateFields.put("unitPrice", unitPrice);
            Object buildingType = row.get("buildingType");
            updateFields.put("buildingType", buildingType);
            Object propertyType = row.get("propertyType");
            updateFields.put("propertyType", propertyType);
            String propertyYears = row.get("propertyYears").toString();
            updateFields.put("propertyYears", propertyYears);

            //把updateFields的字段更新到extinfo中
            // 构建SQL更新语句，将updateFields的字段更新到extinfo中
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("UPDATE hb_page_info SET extinfo = JSON_SET(extinfo, ");

            // 遍历updateFields，将每个字段添加到SQL语句中
            for (Map.Entry<String, Object> entry : updateFields.entrySet()) {
                String fieldName = entry.getKey();
                Object fieldValue = entry.getValue();
                // 构建JSON_SET函数的一部分，设置字段名和新值
                //如果是数字，则不用加单引号
                if(fieldValue instanceof String) {
                    sqlBuilder.append(String.format("'$.%s', '%s', ", fieldName, fieldValue));
                } else {
                    sqlBuilder.append(String.format("'$.%s', %s, ", fieldName, fieldValue));
                }
            }

            // 移除最后一个逗号和空格
            sqlBuilder.setLength(sqlBuilder.length() - 2);
            sqlBuilder.append(")");

            // 添加WHERE子句，以便只更新特定ID的记录
            sqlBuilder.append(" WHERE id = ").append(id).append(";");

            String sql = sqlBuilder.toString();
            String sql2 = StrUtil.format("update dh_project set projectgroupid = {} where sale_org_id = '{}';", groupId, saleOrgId);
            Console.log(sql);
            Console.log(sql2);
            //DBUtil.update(DB_YDAC, sql);
        }
    }

}
